package server.demo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import server.demo.entity.Seat;

import java.util.List;
import java.util.Optional;

@Repository
public interface SeatRepository extends JpaRepository<Seat, Long> {
    List<Seat> findByCarriageIdOrderBySeatNumber(Long carriageId);
    
    Optional<Seat> findByCarriageIdAndSeatNumber(Long carriageId, String seatNumber);
    
    boolean existsByCarriageIdAndSeatNumber(Long carriageId, String seatNumber);
    
    @Query("SELECT s FROM Seat s WHERE s.carriage.train.id = :trainId ORDER BY s.carriage.carriageNumber, s.seatNumber")
    List<Seat> findSeatsByTrainId(@Param("trainId") Long trainId);
    
    @Query("SELECT s FROM Seat s WHERE s.carriage.train.id = :trainId AND s.isAvailable = true")
    List<Seat> findAvailableSeatsByTrainId(@Param("trainId") Long trainId);
    
    @Query("SELECT s FROM Seat s WHERE s.carriage.id = :carriageId AND s.isAvailable = true")
    List<Seat> findAvailableSeatsByCarriageId(@Param("carriageId") Long carriageId);
    
    @Query("SELECT COUNT(s) FROM Seat s WHERE s.carriage.train.id = :trainId AND s.isAvailable = true")
    Long countAvailableSeatsByTrainId(@Param("trainId") Long trainId);
    
    // 统计方法
    long countByIsAvailable(boolean isAvailable);
    
    @Query("SELECT COUNT(s) FROM Seat s WHERE s.carriage.train.id = :trainId")
    long countByTrainId(@Param("trainId") Long trainId);
    
    @Query("SELECT COUNT(s) FROM Seat s WHERE s.carriage.train.id = :trainId AND s.isAvailable = :isAvailable")
    long countByTrainIdAndIsAvailable(@Param("trainId") Long trainId, @Param("isAvailable") boolean isAvailable);
}